SET NOCOUNT ON
go
if exists (select * from sys.procedures where name='showDiskSpace' and
schema_name(schema_id)='utility')
BEGIN
	drop procedure utility.showDiskSpace
END
go
/***************************************************************************************************
*Created/Source by	:sfibich
*ScriptName			:utility.procedure.showDiskSpace.sql
*Version			:1.0.120121
*Purpose			:creates a view that shows drive space from the SQL Server point
*					 of view.  This will only return drives for which SQL Server
*					 has an intrest in, this means that a data file or log file
*					 need to be located on the drive.  
*Notes:	
*		drive						- disk drive letter
*		xp_fixedDrive_free_space_mb - drive space returned by xp_fixedDrives
*		db_size_on_disk_mb			- the total database space allocated on a given drive
*		total_db_drive_space_mb		- xp_frixedDrive_free_space_mb+db_size_on_disk_mb
*		db_used_mb					- the space used by database objects+the log file size if any
*		optimistic_unused_space_on_disk_mb - total_db_drive_space_mb-db_used_mb
*		log_space_mb				- the space allocated to all log files on the drive
***************************************************************************************************/
create procedure utility.showDiskSpace 
as

declare @databaseSpace as table (
database_name sysname not null
,drive char(1) not null
,total_pages bigInt
,used_pages bigInt
,data_pages bigInt
)
insert into @databaseSpace
exec msdb.dbo.sp_msForEachDb @command1=N'use ?;
        select
        db_name()
        ,left(physical_name,1) drive
        ,SUM(total_pages) as total_pages
        ,SUM(used_pages) as used_pages
        ,SUM(data_pages) as data_pages
        from
        sys.allocation_units au
        inner join
        sys.data_spaces ds
        on
        au.data_space_id =ds.data_space_id
        inner join
        sys.database_files df
        on
        ds.data_space_id = df.data_space_id
        group by
        left(physical_name,1)'


/*total free space on the drives*/
declare @fixedDrives as table (
drive char(1)
,mb_free int
)
insert into @fixedDrives
exec xp_fixeddrives

select
fd.drive
,max(fd.mb_free) xp_fixedDrive_free_space_mb
,coalesce(sum(dd.size_on_disk_mb),0) as db_size_on_disk_mb
,max(fd.mb_free) + coalesce(sum(dd.size_on_disk_mb),0)  as
total_db_drive_space_mb
,coalesce(max(oa.db_used_mb),0)+sum(case dd.type_desc when 'log' then
coalesce(dd.size_on_disk_mb,0) else 0 end)   as db_used_mb 
,max(fd.mb_free) + coalesce(sum(dd.size_on_disk_mb),0)-
coalesce(max(oa.db_used_mb),0)-sum(case dd.type_desc when 'log' then
coalesce(dd.size_on_disk_mb,0) else 0 end) as
optimistic_unused_space_on_disk_mb
,sum(case dd.type_desc when 'log' then coalesce(dd.size_on_disk_mb,0)
else 0 end) as log_space_mb
from @fixedDrives fd
left outer join
(
        select
        left(physical_name,1) drive
        ,type_desc
        ,sum(size)*8/1024 size_on_disk_mb
        from sys.master_files
        group by
        left(physical_name,1)
        ,type_desc
) dd
on
fd.drive =dd.drive
left outer join
(
        select
        drive
        ,sum(data_pages)*8/1024 db_data_mb
        ,sum(total_pages)*8/1024 db_total_mb
        ,sum(used_pages)*8/1024  db_used_mb
        from
        @databaseSpace
        group by
        drive
) oa
on
oa.drive =fd.drive
group by
fd.drive
order by
fd.drive
go




if exists (select 1 from sys.objects where name='showDiskSpace' and SCHEMA_NAME(schema_id)='utility')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'utility.showDiskSpace' as info_desc
	,'1.0.120121' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END
